{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a5f54665-3f32-4d04-abd8-6cfa2386a322",
   "metadata": {},
   "source": [
    "# Resource Graph Explorer Data Provider\n",
    "\n",
    "## Description\n",
    "This data provider allows for a connection to the [Azure Resource Graph](https://docs.microsoft.com/azure/governance/resource-graph/overview) and a way to query against the [Azure Resource Graph Explorer](https://docs.microsoft.com/azure/governance/resource-graph/first-query-portal). The data connector functions in the same way as other data connectors and uses the Kusto Query Language (KQL) and has with some subtle differences to other connectors in they way that authentication is handled.\n",
    "\n",
    "You would use this data connector to flexibly and quickly get details on deployed Azure resources within a subscription. It allows for bulk queries on various aspects of resources and returns data in a very structured format. This makes it much more effective and efficient than getting resource specific details via the resource API.\n",
    "\n",
    "More details about data providers in MSTICPy can be found in [the documentation](https://msticpy.readthedocs.io/en/latest/data_acquisition/DataProviders.html)\n",
    "\n",
    "### Installation\n",
    "Installation of this data connector requires that MSTICPy be installed with the Azure extras:\n",
    "`pip install msticpy['azure']`\n",
    "\n",
    "### Initialization\n",
    "The provider for the Azure Resource Graph is named `ResourceGraph`\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "83457852-f9b1-42b6-895e-7102be7c0db5",
   "metadata": {},
   "outputs": [],
   "source": [
    "from msticpy.data.data_providers import QueryProvider\n",
    "qry_prov = QueryProvider(\"ResourceGraph\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51a2e987-b940-4b05-bf18-034210028628",
   "metadata": {},
   "source": [
    "### Authentication\n",
    "Once initialized the first step in using the data provider is to authenticate. The Resource Graph provider uses MSTICPy's [Azure authentication features](https://msticpy.readthedocs.io/en/latest/data_acquisition/AzureData.html?highlight=azure#instantiating-and-connecting-with-an-azure-data-connector) and you can provide a set of authentication methods when connecting. By default the provider will attempt to authenticate using credentials stored in msticpyconfig.yaml (or as environment variables) and an Azure CLI connection but this can be customized with the 'auth_methods' keyword.<br>\n",
    "\n",
    "If storing details in msticpyconfig.yaml they must be under the `AzureCLI` DataProviders section - for more details see [this documentation](https://msticpy.readthedocs.io/en/latest/getting_started/msticpyconfig.html).\n",
    "\n",
    "Once successfully connected you will be presented with a \"Connected\" message.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "399f8801-2ee5-4bd7-a037-e3ab9968d70c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected\n"
     ]
    }
   ],
   "source": [
    "qry_prov.connect(auth_methods=[\"cli\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7497d92-183e-4ed8-b35b-ec97e8122c53",
   "metadata": {},
   "source": [
    "## Listing available queries\n",
    "As with other data providers there are a number of built-in queries with this provider. Once connected you can view the available queries with `QUERY_PROVIDER.list_queries()`.\n",
    "\n",
    "Alternatively you can view query details in an interactive widget with `QUERY_PROVIDER.browse_queries()`\n",
    "\n",
    "For more information, refer documentation : [Listing available queries](https://msticpy.readthedocs.io/en/latest/data_acquisition/DataProviders.html#listing-available-queries)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "a7da0ae6-b14d-49f6-ab40-d143d74ecc68",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "5a10e2d1d1d3481691eeaf9cc9420080",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "VBox(children=(Text(value='', description='Filter:', style=DescriptionStyle(description_width='initial')), Sel…"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<hr>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<h3>Retrieves list of VMs with network details</h3><div></div><p><b>Parameters</b></p><div>add_query_items: str (optional)</div><div style='margin-left: 40px'>    Additional query clauses</div><div>host_name: str (optional)</div><div style='margin-left: 40px'>    A hostname to filter on</div><div>table: str (optional)</div><div style='margin-left: 40px'>    Table name</div><div style='margin-left: 40px'>    (default value is: resources)</div><br><p><b>Query</b></p><pre>{table} \n",
       "| where type =~ \"microsoft.compute/virtualmachines\" \n",
       "| where name contains \"{host_name}\" \n",
       "| extend nics=array_length(properties.networkProfile.networkInterfaces) \n",
       "| mv-expand nic=properties.networkProfile.networkInterfaces \n",
       "| where nics == 1 or nic.properties.primary =~ \"true\" or isempty(nic) \n",
       "| project vmId = id, vmName = name,\n",
       "  vmSize=tostring(properties.hardwareProfile.vmSize), nicId =\n",
       "  tostring(nic.id)\n",
       "| join kind=leftouter ( Resources \n",
       "| where type =~ \"microsoft.network/networkinterfaces\" \n",
       "| extend ipConfigsCount=array_length(properties.ipConfigurations) \n",
       "| mv-expand ipconfig=properties.ipConfigurations \n",
       "| where ipConfigsCount == 1 or ipconfig.properties.primary =~ \"true\" \n",
       "| project nicId = id, publicIpId =\n",
       "  tostring(ipconfig.properties.publicIPAddress.id)) on nicId\n",
       "| project-away nicId1 \n",
       "| summarize by vmId, vmName, vmSize, nicId, publicIpId \n",
       "| join kind=leftouter ( Resources \n",
       "| where type =~ \"microsoft.network/publicipaddresses\" \n",
       "| project publicIpId = id, publicIpAddress = properties.ipAddress) on publicIpId \n",
       "| project-away publicIpId1 {add_query_items}</pre><br>\n",
       "        <p><b>Example</b></p>\n",
       "        <p>{QueryProvider}[.QueryPath].QueryName(params...)</p>\n",
       "        <pre>qry_prov.ResourceGraph.list_detailed_virtual_machines(start=start, end=end, hostname=host)</pre>\n",
       "    "
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "qry_prov.browse_queries()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7bb7896-296f-45e3-b211-b4fc35e15bc2",
   "metadata": {},
   "source": [
    "## Running pre-defined query\n",
    "In order to run pre-defined query, execute with the query name, e.g. `QUERY_PROVIDER.ResoruceGraph.QUERY_NAME()`. You can pass parameters to these queries to customize them, however they will also run with default parameters if none as provider. The query browser will provide details as to what parameters are avaliable with each query.\n",
    "\n",
    "As with other data providers data is returned to you in a Pandas DataFrame.\n",
    "\n",
    "For more information , refer documentation - [Running an pre-defined query](https://msticpy.readthedocs.io/en/latest/data_acquisition/DataProviders.html#running-an-pre-defined-query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "e852f7f5-501c-4122-ac09-7594a38751e9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>apiVersion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>microsoft.alertsmanagement/actionrules</td>\n",
       "      <td>2019-05-05-preview</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>microsoft.alertsmanagement/smartdetectoralertr...</td>\n",
       "      <td>2021-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>microsoft.apimanagement/service</td>\n",
       "      <td>2019-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>microsoft.automanage/accounts</td>\n",
       "      <td>2020-06-30-preview</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>microsoft.automation/automationaccounts</td>\n",
       "      <td>2018-06-30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>microsoft.web/serverfarms</td>\n",
       "      <td>2020-10-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>microsoft.web/sites</td>\n",
       "      <td>2019-08-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>163</th>\n",
       "      <td>microsoft.web/sites/slots</td>\n",
       "      <td>2019-08-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>microsoft.web/staticsites</td>\n",
       "      <td>2019-12-01-preview</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>165</th>\n",
       "      <td>sendgrid.email/accounts</td>\n",
       "      <td>2015-01-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>166 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  type          apiVersion\n",
       "0               microsoft.alertsmanagement/actionrules  2019-05-05-preview\n",
       "1    microsoft.alertsmanagement/smartdetectoralertr...          2021-04-01\n",
       "2                      microsoft.apimanagement/service          2019-12-01\n",
       "3                        microsoft.automanage/accounts  2020-06-30-preview\n",
       "4              microsoft.automation/automationaccounts          2018-06-30\n",
       "..                                                 ...                 ...\n",
       "161                          microsoft.web/serverfarms          2020-10-01\n",
       "162                                microsoft.web/sites          2019-08-01\n",
       "163                          microsoft.web/sites/slots          2019-08-01\n",
       "164                          microsoft.web/staticsites  2019-12-01-preview\n",
       "165                            sendgrid.email/accounts          2015-01-01\n",
       "\n",
       "[166 rows x 2 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "qry_prov.ResourceGraph.list_resources_by_api_version()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "862952b2-d631-4759-a9aa-5135ebfa43bc",
   "metadata": {},
   "source": [
    "### Running an ad-hoc query\n",
    "You can also define a your own KQL query for the Resource Graph and run with `QUERY_PROVIDER.exec_query(QUERY)`\n",
    "\n",
    "For more information, see the documentation on [Running an Ad-hoc Query](https://msticpy.readthedocs.io/en/latest/data_acquisition/DataProviders.html#running-an-ad-hoc-query)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "1a7fbec9-8ca4-4f00-ad09-3c0aedff310e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>tenantId</th>\n",
       "      <th>kind</th>\n",
       "      <th>location</th>\n",
       "      <th>resourceGroup</th>\n",
       "      <th>subscriptionId</th>\n",
       "      <th>managedBy</th>\n",
       "      <th>sku</th>\n",
       "      <th>...</th>\n",
       "      <th>tags.azsecpack</th>\n",
       "      <th>identity.userAssignedIdentities./subscriptions/8eebd9ad-e271-4989-a796-d60c57655743/resourceGroups/AzSecPackAutoConfigRG/providers/Microsoft.ManagedIdentity/userAssignedIdentities/AzSecPackAutoConfigUA-eastus2.principalId</th>\n",
       "      <th>identity.userAssignedIdentities./subscriptions/8eebd9ad-e271-4989-a796-d60c57655743/resourceGroups/AzSecPackAutoConfigRG/providers/Microsoft.ManagedIdentity/userAssignedIdentities/AzSecPackAutoConfigUA-eastus2.clientId</th>\n",
       "      <th>identity.type</th>\n",
       "      <th>identity</th>\n",
       "      <th>properties.storageProfile.osDisk.vhd.uri</th>\n",
       "      <th>properties.osProfile.windowsConfiguration.patchSettings.patchMode</th>\n",
       "      <th>properties.osProfile.windowsConfiguration.provisionVMAgent</th>\n",
       "      <th>properties.osProfile.windowsConfiguration.enableAutomaticUpdates</th>\n",
       "      <th>properties.diagnosticsProfile.bootDiagnostics.storageUri</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/subscriptions/8eebd9ad-e271-4989-a796-d60c576...</td>\n",
       "      <td>RHEL77Base</td>\n",
       "      <td>microsoft.compute/virtualmachines</td>\n",
       "      <td>72f988bf-86f1-41af-91ab-2d7cd011db47</td>\n",
       "      <td></td>\n",
       "      <td>eastus2</td>\n",
       "      <td>linuxtestlab</td>\n",
       "      <td>8eebd9ad-e271-4989-a796-d60c57655743</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>nonprod</td>\n",
       "      <td>e660337c-1cc7-4818-b8c8-3f005dbc6f2a</td>\n",
       "      <td>5fae63c7-985a-4432-9ff2-ef6ff0dc7db6</td>\n",
       "      <td>UserAssigned</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>/subscriptions/8eebd9ad-e271-4989-a796-d60c576...</td>\n",
       "      <td>Ubuntu18ASC</td>\n",
       "      <td>microsoft.compute/virtualmachines</td>\n",
       "      <td>72f988bf-86f1-41af-91ab-2d7cd011db47</td>\n",
       "      <td></td>\n",
       "      <td>eastus2</td>\n",
       "      <td>linuxtestlab</td>\n",
       "      <td>8eebd9ad-e271-4989-a796-d60c57655743</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>nonprod</td>\n",
       "      <td>e660337c-1cc7-4818-b8c8-3f005dbc6f2a</td>\n",
       "      <td>5fae63c7-985a-4432-9ff2-ef6ff0dc7db6</td>\n",
       "      <td>UserAssigned</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>/subscriptions/8eebd9ad-e271-4989-a796-d60c576...</td>\n",
       "      <td>GodzillaTron1</td>\n",
       "      <td>microsoft.compute/virtualmachines</td>\n",
       "      <td>72f988bf-86f1-41af-91ab-2d7cd011db47</td>\n",
       "      <td></td>\n",
       "      <td>japanwest</td>\n",
       "      <td>monster-island</td>\n",
       "      <td>8eebd9ad-e271-4989-a796-d60c57655743</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://monsterislanddisks868.blob.core.window...</td>\n",
       "      <td>AutomaticByOS</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>https://monsterislanddiag271.blob.core.windows...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3 rows × 58 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  id           name  \\\n",
       "0  /subscriptions/8eebd9ad-e271-4989-a796-d60c576...     RHEL77Base   \n",
       "1  /subscriptions/8eebd9ad-e271-4989-a796-d60c576...    Ubuntu18ASC   \n",
       "2  /subscriptions/8eebd9ad-e271-4989-a796-d60c576...  GodzillaTron1   \n",
       "\n",
       "                                type                              tenantId  \\\n",
       "0  microsoft.compute/virtualmachines  72f988bf-86f1-41af-91ab-2d7cd011db47   \n",
       "1  microsoft.compute/virtualmachines  72f988bf-86f1-41af-91ab-2d7cd011db47   \n",
       "2  microsoft.compute/virtualmachines  72f988bf-86f1-41af-91ab-2d7cd011db47   \n",
       "\n",
       "  kind   location   resourceGroup                        subscriptionId  \\\n",
       "0         eastus2    linuxtestlab  8eebd9ad-e271-4989-a796-d60c57655743   \n",
       "1         eastus2    linuxtestlab  8eebd9ad-e271-4989-a796-d60c57655743   \n",
       "2       japanwest  monster-island  8eebd9ad-e271-4989-a796-d60c57655743   \n",
       "\n",
       "  managedBy   sku  ... tags.azsecpack  \\\n",
       "0            None  ...        nonprod   \n",
       "1            None  ...        nonprod   \n",
       "2            None  ...            NaN   \n",
       "\n",
       "  identity.userAssignedIdentities./subscriptions/8eebd9ad-e271-4989-a796-d60c57655743/resourceGroups/AzSecPackAutoConfigRG/providers/Microsoft.ManagedIdentity/userAssignedIdentities/AzSecPackAutoConfigUA-eastus2.principalId  \\\n",
       "0               e660337c-1cc7-4818-b8c8-3f005dbc6f2a                                                                                                                                                                              \n",
       "1               e660337c-1cc7-4818-b8c8-3f005dbc6f2a                                                                                                                                                                              \n",
       "2                                                NaN                                                                                                                                                                              \n",
       "\n",
       "  identity.userAssignedIdentities./subscriptions/8eebd9ad-e271-4989-a796-d60c57655743/resourceGroups/AzSecPackAutoConfigRG/providers/Microsoft.ManagedIdentity/userAssignedIdentities/AzSecPackAutoConfigUA-eastus2.clientId  \\\n",
       "0               5fae63c7-985a-4432-9ff2-ef6ff0dc7db6                                                                                                                                                                           \n",
       "1               5fae63c7-985a-4432-9ff2-ef6ff0dc7db6                                                                                                                                                                           \n",
       "2                                                NaN                                                                                                                                                                           \n",
       "\n",
       "  identity.type identity           properties.storageProfile.osDisk.vhd.uri  \\\n",
       "0  UserAssigned      NaN                                                NaN   \n",
       "1  UserAssigned      NaN                                                NaN   \n",
       "2           NaN      NaN  https://monsterislanddisks868.blob.core.window...   \n",
       "\n",
       "  properties.osProfile.windowsConfiguration.patchSettings.patchMode  \\\n",
       "0                                                NaN                  \n",
       "1                                                NaN                  \n",
       "2                                      AutomaticByOS                  \n",
       "\n",
       "  properties.osProfile.windowsConfiguration.provisionVMAgent  \\\n",
       "0                                                NaN           \n",
       "1                                                NaN           \n",
       "2                                               True           \n",
       "\n",
       "  properties.osProfile.windowsConfiguration.enableAutomaticUpdates  \\\n",
       "0                                                NaN                 \n",
       "1                                                NaN                 \n",
       "2                                               True                 \n",
       "\n",
       "  properties.diagnosticsProfile.bootDiagnostics.storageUri  \n",
       "0                                                NaN        \n",
       "1                                                NaN        \n",
       "2  https://monsterislanddiag271.blob.core.windows...        \n",
       "\n",
       "[3 rows x 58 columns]"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "query = \"Resources | where type =~ 'Microsoft.Compute/virtualMachines' | take 3\"\n",
    "qry_prov.exec_query(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8cee1401-26c8-45cf-b184-7d6a67d8b447",
   "metadata": {},
   "source": [
    "<div class=\"alert alert-block alert-info\">\n",
    "<b>Note:</b> Resource Graph queries are limited to 1000 rows of output each. <br>\n",
    "If your query returns 1000 rows it is likely it has hit this limit, consider re-writing the query to return a smaller subset of data. <br>\n",
    "This applies to both built in queries and ad-hoc queries.<br>\n",
    "</div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c7a0e691-a114-44ff-8000-74f259262ef1",
   "metadata": {},
   "source": [
    "## End-to-end Example\n",
    "\n",
    "In this example we want to take a look at all of the virtual machines we have in our environment and they get specific details including public IP on one of them:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "03d85748-1d44-453a-b3e9-4d69277e6e19",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connected\n"
     ]
    }
   ],
   "source": [
    "from msticpy.data.data_providers import QueryProvider\n",
    "# Initialize and connect to provider\n",
    "qry_prov = QueryProvider(\"ResourceGraph\")\n",
    "qry_prov.connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "41281a4f-a717-41fe-84d1-5df00f1fdff1",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of VMs found : 418\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>name</th>\n",
       "      <th>type</th>\n",
       "      <th>tenantId</th>\n",
       "      <th>kind</th>\n",
       "      <th>location</th>\n",
       "      <th>resourceGroup</th>\n",
       "      <th>subscriptionId</th>\n",
       "      <th>managedBy</th>\n",
       "      <th>sku</th>\n",
       "      <th>...</th>\n",
       "      <th>properties.extended.instanceView.powerState.displayStatus</th>\n",
       "      <th>properties.extended.instanceView.powerState.level</th>\n",
       "      <th>properties.extended.instanceView.powerState.code</th>\n",
       "      <th>properties.vmId</th>\n",
       "      <th>properties.diagnosticsProfile.bootDiagnostics.enabled</th>\n",
       "      <th>tags.platformsettings.host_environment.service.platform_optedin_for_rootcerts</th>\n",
       "      <th>tags.azsecpack</th>\n",
       "      <th>identity.principalId</th>\n",
       "      <th>identity.tenantId</th>\n",
       "      <th>identity.type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>/subscriptions/40dcc8bf-0478-4f3b-b275-ed0a94f...</td>\n",
       "      <td>MSTIC-DSVM</td>\n",
       "      <td>microsoft.compute/virtualmachines</td>\n",
       "      <td>72f988bf-86f1-41af-91ab-2d7cd011db47</td>\n",
       "      <td></td>\n",
       "      <td>eastus</td>\n",
       "      <td>msticpy</td>\n",
       "      <td>40dcc8bf-0478-4f3b-b275-ed0a94f2c013</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td>...</td>\n",
       "      <td>VM deallocated</td>\n",
       "      <td>Info</td>\n",
       "      <td>PowerState/deallocated</td>\n",
       "      <td>280b7966-c42f-4730-b993-62bef12b187d</td>\n",
       "      <td>True</td>\n",
       "      <td>true</td>\n",
       "      <td>nonprod</td>\n",
       "      <td>7eece21d-835f-432e-b049-2c3002f3879e</td>\n",
       "      <td>72f988bf-86f1-41af-91ab-2d7cd011db47</td>\n",
       "      <td>SystemAssigned, UserAssigned</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1 rows × 46 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                  id        name  \\\n",
       "0  /subscriptions/40dcc8bf-0478-4f3b-b275-ed0a94f...  MSTIC-DSVM   \n",
       "\n",
       "                                type                              tenantId  \\\n",
       "0  microsoft.compute/virtualmachines  72f988bf-86f1-41af-91ab-2d7cd011db47   \n",
       "\n",
       "  kind location resourceGroup                        subscriptionId managedBy  \\\n",
       "0        eastus       msticpy  40dcc8bf-0478-4f3b-b275-ed0a94f2c013             \n",
       "\n",
       "    sku  ... properties.extended.instanceView.powerState.displayStatus  \\\n",
       "0  None  ...                                     VM deallocated          \n",
       "\n",
       "  properties.extended.instanceView.powerState.level  \\\n",
       "0                                              Info   \n",
       "\n",
       "  properties.extended.instanceView.powerState.code  \\\n",
       "0                           PowerState/deallocated   \n",
       "\n",
       "                        properties.vmId  \\\n",
       "0  280b7966-c42f-4730-b993-62bef12b187d   \n",
       "\n",
       "  properties.diagnosticsProfile.bootDiagnostics.enabled  \\\n",
       "0                                               True      \n",
       "\n",
       "  tags.platformsettings.host_environment.service.platform_optedin_for_rootcerts  \\\n",
       "0                                               true                              \n",
       "\n",
       "  tags.azsecpack                  identity.principalId  \\\n",
       "0        nonprod  7eece21d-835f-432e-b049-2c3002f3879e   \n",
       "\n",
       "                      identity.tenantId                 identity.type  \n",
       "0  72f988bf-86f1-41af-91ab-2d7cd011db47  SystemAssigned, UserAssigned  \n",
       "\n",
       "[1 rows x 46 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Get list of VMs and see how many we have\n",
    "vms = qry_prov.ResourceGraph.list_virtual_machines()\n",
    "print(f\"Number of VMs found : {len(vms.index)}\")\n",
    "# Filter the query to get a smaller dataset\n",
    "vms = qry_prov.ResourceGraph.list_virtual_machines(add_query_items=\"| where resourceGroup contains 'msticpy'\")\n",
    "display(vms)\n",
    "# Set hostname for our next query\n",
    "hostname = vms.iloc[0]['name']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2dfd1fef-a14a-409a-a5f9-6bb53458527f",
   "metadata": {},
   "source": [
    "Now we can get details on the specific VM using its hostname."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "a94a0680-1036-4d05-8cf4-7b38f23886ab",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "=================================================================================================================================  ==========  ============  ======================================================================================================================================  ======================================================================================================================================  =================\n",
      "vmId                                                                                                                               vmName      vmSize        nicId                                                                                                                                   publicIpId                                                                                                                              publicIpAddress\n",
      "=================================================================================================================================  ==========  ============  ======================================================================================================================================  ======================================================================================================================================  =================\n",
      "/subscriptions/40dcc8bf-0478-4f3b-b275-ed0a94f2c013/resourceGroups/MSTICpy/providers/Microsoft.Compute/virtualMachines/MSTIC-DSVM  MSTIC-DSVM  Standard_B2s  /subscriptions/40dcc8bf-0478-4f3b-b275-ed0a94f2c013/resourceGroups/MSTICpy/providers/Microsoft.Network/networkInterfaces/mstic-dsvm832  /subscriptions/40dcc8bf-0478-4f3b-b275-ed0a94f2c013/resourceGroups/MSTICpy/providers/Microsoft.Network/publicIPAddresses/MSTIC-DSVM-ip  20.55.96.194\n",
      "=================================================================================================================================  ==========  ============  ======================================================================================================================================  ======================================================================================================================================  =================\n"
     ]
    }
   ],
   "source": [
    "df = qry_prov.ResourceGraph.list_detailed_virtual_machines(host_name=hostname)\n",
    "\n",
    "print(tabulate(df.head(), df.columns, tablefmt=\"rst\", showindex=False))\n",
    "\n",
    "# copy table to clipboard and paste in RST doc\n",
    "clip.copy(tabulate(df.head(), df.columns, tablefmt=\"rst\", showindex=False))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f9af1b5-683b-46e4-92ff-9a05d4b13a93",
   "metadata": {},
   "source": [
    "## References\n",
    "\n",
    "- Azure Resource Graph: https://docs.microsoft.com/azure/governance/resource-graph/overview\n",
    "- Resource Graph Query Language: https://docs.microsoft.com/azure/governance/resource-graph/concepts/query-language"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {
     "177cea49a53a43219886aed023d1d1e3": {
      "model_module": "@jupyter-widgets/base",
      "model_module_version": "1.2.0",
      "model_name": "LayoutModel",
      "state": {}
     },
     "235a53ea6abb45979ae7b91fbff1e5fb": {
      "model_module": "@jupyter-widgets/base",
      "model_module_version": "1.2.0",
      "model_name": "LayoutModel",
      "state": {
       "height": "300px",
       "width": "50%"
      }
     },
     "34ead46181f74ca9b2f28c74a9e8a252": {
      "model_module": "@jupyter-widgets/controls",
      "model_module_version": "1.5.0",
      "model_name": "DescriptionStyleModel",
      "state": {
       "description_width": "initial"
      }
     },
     "3a227f09e6ea4f5aa9d3781ffce26274": {
      "model_module": "@jupyter-widgets/controls",
      "model_module_version": "1.5.0",
      "model_name": "DescriptionStyleModel",
      "state": {
       "description_width": "initial"
      }
     },
     "4be04d0eb6954df29ccba9efe619eba9": {
      "model_module": "@jupyter-widgets/controls",
      "model_module_version": "1.5.0",
      "model_name": "SelectModel",
      "state": {
       "_options_labels": [
        "ResourceGraph.list_detailed_virtual_machines",
        "ResourceGraph.list_public_ips",
        "ResourceGraph.list_resources",
        "ResourceGraph.list_resources_by_api_version",
        "ResourceGraph.list_resources_by_type",
        "ResourceGraph.list_virtual_machines"
       ],
       "description": "Select an item",
       "index": 0,
       "layout": "IPY_MODEL_8bc9addf7cf34eacbb363393be0c3766",
       "style": "IPY_MODEL_99ede81bf17044dfbe37c95e5fa30458"
      }
     },
     "503bd34f33b94a7a8ab939991a693866": {
      "model_module": "@jupyter-widgets/base",
      "model_module_version": "1.2.0",
      "model_name": "LayoutModel",
      "state": {
       "width": "95%"
      }
     },
     "5a10e2d1d1d3481691eeaf9cc9420080": {
      "model_module": "@jupyter-widgets/controls",
      "model_module_version": "1.5.0",
      "model_name": "VBoxModel",
      "state": {
       "children": [
        "IPY_MODEL_c84ab0195d1b453b938a2d7bc62e78bb",
        "IPY_MODEL_e96753f8b28c4ba6a1699e5824a3172e"
       ],
       "layout": "IPY_MODEL_177cea49a53a43219886aed023d1d1e3"
      }
     },
     "732eeea243ba4ee0afd66bff6e7c182b": {
      "model_module": "@jupyter-widgets/controls",
      "model_module_version": "1.5.0",
      "model_name": "DescriptionStyleModel",
      "state": {
       "description_width": "initial"
      }
     },
     "73f73bc8c2614403a5894acef9e68f2d": {
      "model_module": "@jupyter-widgets/base",
      "model_module_version": "1.2.0",
      "model_name": "LayoutModel",
      "state": {}
     },
     "7b82fa165eab47ec8cb6f8eb71a24139": {
      "model_module": "@jupyter-widgets/base",
      "model_module_version": "1.2.0",
      "model_name": "LayoutModel",
      "state": {}
     },
     "8bc9addf7cf34eacbb363393be0c3766": {
      "model_module": "@jupyter-widgets/base",
      "model_module_version": "1.2.0",
      "model_name": "LayoutModel",
      "state": {
       "height": "300px",
       "width": "50%"
      }
     },
     "99ede81bf17044dfbe37c95e5fa30458": {
      "model_module": "@jupyter-widgets/controls",
      "model_module_version": "1.5.0",
      "model_name": "DescriptionStyleModel",
      "state": {
       "description_width": "initial"
      }
     },
     "b4a5cfd93dae4548970b6d78be9e19ab": {
      "model_module": "@jupyter-widgets/base",
      "model_module_version": "1.2.0",
      "model_name": "LayoutModel",
      "state": {}
     },
     "c84ab0195d1b453b938a2d7bc62e78bb": {
      "model_module": "@jupyter-widgets/controls",
      "model_module_version": "1.5.0",
      "model_name": "TextModel",
      "state": {
       "description": "Filter:",
       "layout": "IPY_MODEL_73f73bc8c2614403a5894acef9e68f2d",
       "style": "IPY_MODEL_732eeea243ba4ee0afd66bff6e7c182b"
      }
     },
     "d51df8f199544b22a6040e809ebe8a1a": {
      "model_module": "@jupyter-widgets/controls",
      "model_module_version": "1.5.0",
      "model_name": "VBoxModel",
      "state": {
       "children": [
        "IPY_MODEL_ddc3b15c50a64049851a33986a1d65e5",
        "IPY_MODEL_4be04d0eb6954df29ccba9efe619eba9"
       ],
       "layout": "IPY_MODEL_b4a5cfd93dae4548970b6d78be9e19ab"
      }
     },
     "ddc3b15c50a64049851a33986a1d65e5": {
      "model_module": "@jupyter-widgets/controls",
      "model_module_version": "1.5.0",
      "model_name": "TextModel",
      "state": {
       "description": "Filter:",
       "layout": "IPY_MODEL_7b82fa165eab47ec8cb6f8eb71a24139",
       "style": "IPY_MODEL_34ead46181f74ca9b2f28c74a9e8a252"
      }
     },
     "e96753f8b28c4ba6a1699e5824a3172e": {
      "model_module": "@jupyter-widgets/controls",
      "model_module_version": "1.5.0",
      "model_name": "SelectModel",
      "state": {
       "_options_labels": [
        "ResourceGraph.list_detailed_virtual_machines",
        "ResourceGraph.list_public_ips",
        "ResourceGraph.list_resources",
        "ResourceGraph.list_resources_by_api_version",
        "ResourceGraph.list_resources_by_type",
        "ResourceGraph.list_virtual_machines"
       ],
       "description": "Select an item",
       "index": 0,
       "layout": "IPY_MODEL_235a53ea6abb45979ae7b91fbff1e5fb",
       "style": "IPY_MODEL_3a227f09e6ea4f5aa9d3781ffce26274"
      }
     }
    },
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
